package com.TEight.Forum.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.TEight.Forum.dao.CategoryDao;
import com.TEight.Forum.pojo.Category;
import com.TEight.Forum.util.DBPoolUtil;

public class CategoryDaoImpl implements CategoryDao {
    @Override
    public List<Category> getCategoryList() {
        List<Category> list = new ArrayList<>();
        String sql = "select* from category ";
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = DBPoolUtil.getConnection();
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                Category category = new Category();
                category.setCategoryId(resultSet.getInt(1));
                category.setCategoryName(resultSet.getString(2));
                list.add(category);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBPoolUtil.allClose(resultSet, statement, connection);
        }
        return list;
    }

    @Override
    public Category getEditCategory(String categoryId) {
        Category category = new Category();
        String sql = "select categoryId, categoryName from category where categoryId = ?";
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = DBPoolUtil.getConnection();
            statement = connection.prepareStatement(sql);
            statement.setInt(1, Integer.parseInt(categoryId));
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                category.setCategoryId(resultSet.getInt(1));
                category.setCategoryName(resultSet.getString(2));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBPoolUtil.allClose(resultSet, statement, connection);
        }
        return category;
    }

    @Override
    public void update(Category category) {
        String sql = "update category set categoryName = ? where categoryId = ?";
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = DBPoolUtil.getConnection();
            statement = connection.prepareStatement(sql);
            statement.setString(1, category.getCategoryName());
            statement.setInt(2, category.getCategoryId());
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBPoolUtil.allClose(resultSet, statement, connection);
        }
    }

    @Override
    public void delete(String categoryId) {
        String sql = "delete from category where categoryId = ?";
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = DBPoolUtil.getConnection();
            statement = connection.prepareStatement(sql);
            statement.setInt(1, Integer.parseInt(categoryId));
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBPoolUtil.allClose(resultSet, statement, connection);
        }
    }

    @Override
    public void add(Category category) {
        String sql = "insert into category(categoryId, categoryName) values(?, ?)";
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = DBPoolUtil.getConnection();
            statement = connection.prepareStatement(sql);
            statement.setInt(1, category.getCategoryId());
            statement.setString(2, category.getCategoryName());
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBPoolUtil.allClose(resultSet, statement, connection);
        }
    }

}
